import pymysql

#打开数据库连接
db = pymysql.connect("localhost","root","root","pytest")

# 使用 cursor()方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行SQL查询
#cursor.execute("select version()")

# 使用fetchone() 方法获取单条数据。
#data = cursor.fetchone()
#print("Database version : %s " % data)

# 使用 execute() 方法执行 SQL，如果表存在则删除
#cursor.execute("drop table if exists employee")
# 使用预处理语句创建表

# sql = """create table employee (
#             first_name char(20) not null,
#             last_name char(20),
#             age int,
#             sex char(1),
#             income float
#         )"""

# cursor.execute(sql)

# sql插入语句
'''
sql = """insert into employee(first_name,last_name,age,sex,income)
            values('Mac','Mohan',20,'M',2000)"""
'''
# sql = """insert into employee(first_name,last_name,age,sex,income)
#             values('Mac00','Mohan',20,'M',2000)"""
#下面语句不知何故运行不了
#sql = "insert into employee(firstname,last_name,age,sex,income) values('%s','%s','%d','%c','%d')" % ('Mac00','Mohan00',20,'M',2000)

# # sql 查询语句
# sql = "select * from employee \
#         where income > '%d'" % (1000)

# sql 更新语句
sql = "update employee set age = age + 1 where sex = '%c'" % ('M')
try:
    # 执行sql语句
    cursor.execute(sql)
    # 提交到数据库执行
    db.commit()

    # #执行sql语句
    # cursor.execute(sql)
    # # 获取所有记录列表
    # results = cursor.fetchall()
    # for row in results:
    #     fname = row[0]
    #     lname = row[1]
    #     age = row[2]
    #     sex = row[3]
    #     income = row[4]
    #     # 打印结果
    #     print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname,lname,age,sex,income ))
except:
    # 如果发生错误则回滚
    db.rollback()
    # print("Error: unable to fetch data")



#关闭数据库连接
db.close()

